﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using Missing = System.Reflection.Missing;
using Microsoft.Office.Interop.Excel;
using System.IO;

namespace Citi.Helpers
{
    public class MergeExcel
    {
        Excel.Application app = new Excel.Application();

        //initialize the object of saved target 
        Excel.Workbook bookDest = null;
        Excel.Worksheet sheetDest = null;

        //initialize the object of read data
        Excel.Workbook bookSource = null;
        Excel.Worksheet sheetSource = null;
        string _sourceFile = null;
        string _destFile = string.Empty;
        string _columnEnd = string.Empty;
        int _headerRowCount = 0;
        int _currentRowCount = 0;
        int _ignoreColumns = 0;
        string firstColumn = string.Empty;

        public MergeExcel(string sourceFile, string destFile, string newSheetName, string columnEnd, int headerRowCount, int ignoreColumns)
        {
            //Use class Missing case to indicate the missing value. e.g. when you call the method that has default parameter value
            bookDest = app.Workbooks.Add(Missing.Value);

            sheetDest = bookDest.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet;
            sheetDest.Name = newSheetName;

            foreach (Worksheet worksheet in bookDest.Worksheets.Cast<Worksheet>().Where(x => x.Name != newSheetName))
                worksheet.Delete();

            _sourceFile = sourceFile;
            _destFile = destFile;
            _columnEnd = columnEnd;
            _headerRowCount = headerRowCount;
            _ignoreColumns = ignoreColumns;
            firstColumn = ExcelHelper.GetExcelColumnName(ignoreColumns + 1);
        }

        void OpenBook(string fileName, int sheetIndex)
        {
            if (bookSource == null)
                bookSource = app.Workbooks._Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            if (sheetIndex > 0)
            {
                sheetSource = bookSource.Worksheets[sheetIndex] as Excel.Worksheet;
                if (string.IsNullOrWhiteSpace(_columnEnd))
                {
                    var row = sheetSource.Rows[_headerRowCount];

                    for (var i = 1 + _ignoreColumns; i <= row.Cells.Count; i++)
                    {
                        var lastCell = sheetSource.Cells[_headerRowCount, i] as Range;
                        var nextCell = sheetSource.Cells[_headerRowCount, i + 1] as Range;

                        if ((lastCell.Value2 == null || string.IsNullOrWhiteSpace(lastCell.Value2.ToString()))
                            && (nextCell.Value2 == null || string.IsNullOrWhiteSpace(nextCell.Value2.ToString())))
                        {
                            _columnEnd = ExcelHelper.GetExcelColumnName(i - 1);
                            return;
                        }
                    }
                }
            }
        }

        void CloseBook()
        {
            bookSource.Close(false, Missing.Value, Missing.Value);
        }

        void CopyData()
        {
            int sheetRowCount = sheetSource.UsedRange.Rows.Count;
            Excel.Range range = sheetSource.get_Range(string.Format("{0}{1}", firstColumn, _headerRowCount), string.Concat(_columnEnd, sheetRowCount));
            range.Copy(sheetDest.get_Range(string.Format("A{0}", _currentRowCount), Missing.Value));
            _currentRowCount += range.Rows.Count;
        }

        void Save()
        {
            bookDest.Saved = true;
            bookDest.SaveCopyAs(_destFile);
        }

        void Quit()
        {
            app.Quit();
        }

        public void DoMerge()
        {
            _currentRowCount = 1;

            OpenBook(_sourceFile, 0);
            try
            {
                for (int i = 1; i <= bookSource.Sheets.Count; i++)
                {
                    OpenBook(_sourceFile, i);
                    CopyData();
                    _headerRowCount = 1;
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                CloseBook();
                Save();
                Quit();
            }
        }

        public static string MergeSheets(string filePath, string pathToSave, string newSheetName, int headerRowNumber, int firstColumnNumber)
        {
            var newFilePath = pathToSave.Replace(Path.GetExtension(pathToSave), ".xlsx");
            var newFileLocalPath = Path.Combine(@"C:\Temp", Path.GetFileName(newFilePath));

            if (File.Exists(newFileLocalPath)) File.Delete(newFileLocalPath);

            new Helpers.MergeExcel(filePath, newFileLocalPath, newSheetName, null, headerRowNumber, firstColumnNumber - 1).DoMerge();

            if (File.Exists(newFilePath)) File.Delete(newFilePath);
            File.Copy(newFileLocalPath, newFilePath);

            return newFilePath;
        }

        //public static void DoMerge(string[] sourceFiles, string destFile, string columnEnd, int headerRowCount)
        //{
        //    new MergeExcel(sourceFiles, destFile, columnEnd, headerRowCount).DoMerge();
        //}
    }
}
